package com.thinktank.dao;

import java.sql.*;
import java.util.*;

import com.thinktank.vo.*;

import static com.thinktank.util.DbUtil.*;

public class AlbumDao {
	public Connection con = null;

	public AlbumDao(Connection con) {
		super();
		this.con = con;
	}

	public List<Album> getAllAlbums(String url) {
		String query = "SELECT A.*, B.BD_CONTENT AS AB_CONTENT FROM ALBUM A INNER JOIN BOARD B ON (A.AB_NO = B.BD_NO)"+
							"AND B.BD_WRITER=(SELECT MB_NO FROM MEMBER WHERE MB_PAGE=?)";

		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Album> list = null;

		try {
			ps = con.prepareStatement(query);
			ps.setString(1, url);
			rs = ps.executeQuery();

			list = new ArrayList<Album>();
			while (rs.next()) {
				Album album = new Album();
				album.setNo(rs.getString("AB_NO"));
				album.setTag(rs.getString("AB_TAG"));
				album.setLoc(rs.getString("AB_LOC"));
				album.setSrc(rs.getString("AB_SRC"));
				album.setPath(rs.getString("AB_PATH"));
				album.setContent(rs.getString("AB_CONTENT"));
				album.setKey(rs.getString("AB_KEY"));

				list.add(album);
				System.out.println(album);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close(ps);
			close(rs);
		}

		return list;
	}

	public static String setFilter(String start, String end, String title,
			String tag, String loc) {
		if (start == null && end == null && title == null && tag == null
				&& loc == null) {
			return null;
		}
		StringBuffer filter = new StringBuffer("WHERE ");
		if (start != null && end != null)
			filter.append("(BD_DATE BETWEEN '" + start + "' AND '" + end
					+ "') AND");
		if (start != null)
			filter.append("(BD_DATE > '" + start + "') AND ");
		if (end != null)
			filter.append("(BD_DATE < '" + end + "') AND ");

		if (title != null)
			filter.append("(BD_SUBJECT LIKE '%" + title + "%') AND");
		if (tag != null)
			filter.append("(AB_TAG LIKE '%" + tag + "%') AND");
		if (loc != null)
			filter.append("(AB_LOC LIKE '%" + loc + "%') AND");

		String result = filter.toString();
		if (result.endsWith(" AND"))
			result = filter.substring(0, filter.lastIndexOf(" AND")).toString();

		System.out.println(result);

		return result;
	}

	public int insert(Album album) {
		PreparedStatement psmt = null;
		int result = 0;
		try {
			String temp = "select Max(AB_KEY) from ALBUM";
			psmt = con.prepareStatement(temp);
			ResultSet rs = psmt.executeQuery();
			int num=0;
			if(rs.next())			num = rs.getInt(1);
			else					num = 0;
			String seq = num + 1 + "";
			for (int i = seq.length(); i < 3; i++) 
				seq = "0".concat(seq);
			close(rs);
			
			// 1. DB 내에서 MAX Gno를 받고 +1을 해서 gno 변수에 세팅
			String sql = "INSERT INTO ALBUM VALUES (?, ?, ?, ?, ?, ?)";
			psmt = con.prepareStatement(sql);
			psmt.setString(1, album.getNo());
			psmt.setString(2, album.getTag());
			psmt.setString(3, album.getLoc());
			psmt.setString(4, album.getSrc());
			psmt.setString(5, album.getPath());
			psmt.setString(6, seq);
			result = psmt.executeUpdate();
			close(psmt);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result != 0)
			commit(con);
		return result;
	}
	
	public List<Album> getBDFiles(String no){
		String query = "SELECT * FROM ALBUM WHERE AB_NO=?";

		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Album> list = null;

		try {
			ps = con.prepareStatement(query);
			ps.setString(1, no);
			rs = ps.executeQuery();

			list = new ArrayList<Album>();
			while (rs.next()) {
				Album album = new Album();
				album.setNo(rs.getString("AB_NO"));
				album.setTag(rs.getString("AB_TAG"));
				album.setLoc(rs.getString("AB_LOC"));
				album.setSrc(rs.getString("AB_SRC"));
				album.setPath(rs.getString("AB_PATH"));

				list.add(album);
				System.out.println(album);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close(ps);
			close(rs);
		}

		return list;
	}
	
	public int delete(String path){
		String query = "DELETE FROM ALBUM WHERE AB_PATH=?";
		PreparedStatement ps = null;
		int result = 0;
		try{
			ps = con.prepareStatement(query);
			ps.setString(1, path);
			
			result = ps.executeUpdate();
		} catch(SQLException e){
			e.printStackTrace();
		} finally{
			close(ps);
		}
		return result;
	}
	
	
}
